import pandasdmx as sdmx


from sqlalchemy import Column, String, create_engine
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from multiprocessing import Pool

Base = declarative_base()
engine = create_engine('postgresql+psycopg2://test:1qaz@WSX@localhost:5432/eurostat')

# 定义Maping对象:
class Eurostat(Base):
    # 表的名字:
    __tablename__ = 'eurostat'

    # 表的结构:
    id = Column(String(100), primary_key=True)
    name = Column(String(400))
    data = Column(JSONB)

def fetch_data(id,estat,estat_data_flow,session):
    print("geting " + str(id) + " data !")
    try:
        p_data = estat.data(id).to_pandas()
    except Exception as e:
        print("get " + str(id) + " data error!")
        print(e)
        return


    if len(p_data) > 0:
        print("get data success...")
        eurostat = Eurostat()
        eurostat.id = id
        eurostat.name = estat_data_flow[id].name.localizations["en"]
        eurostat.data = p_data.to_json()
        try:
            session.add(eurostat)
            session.commit()
            print("insert " + str(id) + " success...")
        except Exception as e:
            print("insert " + str(id) + " error...")
            print(e)
            session.rollback()
    else:
        print(str(id) + " data is empty!")


if __name__ == '__main__':
    estat = sdmx.Request('ESTAT')
    Base.metadata.create_all(engine)
    estat_data_flow=estat.dataflow().dataflow
    DBSession = sessionmaker(bind=engine)
    session = DBSession()
    for id in estat_data_flow:
        print("geting " + str(id) + " data !")
        try:
            p_data = estat.data(id).to_pandas()
        except Exception as e:
            print("get "+str(id)+" data error!")
            print(e)
            continue

        if len(p_data)>0:
            print("get data success...")
            eurostat = Eurostat()
            eurostat.id = id
            eurostat.name = estat_data_flow[id].name.localizations["en"]
            eurostat.data = p_data.to_json()
            try:
                session.add(eurostat)
                session.commit()
                print("insert "+str(id)+" success...")
            except Exception as e:
                print("insert "+str(id)+" error...")
                print(e)
                session.rollback()
        else:
            print(str(id) + " data is empty!")


